package com.stm.bi.report;

import com.stm.base.api.RegionRemoteService;
import com.stm.base.dto.query.RegionListQry;
import com.stm.base.dto.vo.RegionDetailVO;
import com.stm.base.dto.vo.RegionVO;
import com.stm.bi.dto.query.RegionalSaleQry;
import com.stm.bi.dto.vo.RegionalCityVO;
import com.stm.bi.dto.vo.RegionalCustomerVO;
import com.stm.bi.model.Chart;
import com.stm.framework.core.util.FileResourceLoader;
import com.stm.framework.dbcp.JdbcService;
import com.stm.framework.dto.Response;
import com.stm.ucenter.api.BizCustomerRemoteService;
import com.stm.ucenter.dto.query.BizCustomerListQry;
import com.stm.ucenter.dto.vo.BizCustomerVO;
import jakarta.annotation.Resource;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.temporal.ChronoUnit;
import java.util.*;
import java.util.stream.Collectors;

import static com.stm.saas.constant.IndexConstants.BUSINESS_ID_FIELD_NAME;

/**
 * @author liuyx
 * @date 2025/2/14 9:09
 */
@Component
public class RegionalProportionOfCustomersToBQueryExe extends AbsChartQueryExe<RegionalSaleQry, Chart<RegionalCustomerVO>> {
    @Resource
    private JdbcService jdbcService;

    @Resource
    private BizCustomerRemoteService bizCustomerRemoteService;

    @Resource
    private RegionRemoteService regionRemoteService;


    @Override
    public Chart<RegionalCustomerVO> query(Long orgId, String orgNo, RegionalSaleQry query) {

        RegionalCustomerVO vo = new RegionalCustomerVO();
        List<RegionalCityVO> cityTableData = new ArrayList<>();
        Chart<RegionalCustomerVO> chart = new Chart<>();
        List<List<Object>> datas = new ArrayList<>();
        List<String> items = chart.getItems();
        // 根据部门id查询该部门下所有B端客户信息
        BizCustomerListQry bizQry = new BizCustomerListQry();
        bizQry.setChildId(query.getDeptId());
        Response<List<BizCustomerVO>> bizResponse = bizCustomerRemoteService.selectList(bizQry);
        if (!bizResponse.isSuccess() || bizResponse.getData().isEmpty()) {
            return chart;
        }
        List<BizCustomerVO> bizCustomers = bizResponse.get();
        // 根据第一次下单时间赋值新老客
        markNewOrOldCustomer(bizCustomers);
        List<String> bizCodes = bizCustomers.stream().map(BizCustomerVO::getCode).filter(Objects::nonNull).collect(Collectors.toList());

        List<Map> maps = getToBCustomerList(bizCustomers, bizCodes,orgId);

        //总客数
        double totalNum = maps.stream().filter(m -> m.get("newOrOld") != null).map(m -> m.get("customer_code")).filter(Objects::nonNull).collect(Collectors.toSet()).size();
        items.add("新客占比");
        items.add("老客占比");
        double newCustomQty = 0;
        double oldCustomQty = 0;
        double totalCustomQty = 0;
        // 查全国
        /*if (query.getRegionCode() == null || query.getRegionCode().isEmpty()) {

            vo.setRegionName("全国").setSalesVolume(BigDecimal.valueOf(100));
            newCustomQty = maps.stream().filter(m -> m.get("newOrOld") != null && m.get("newOrOld").equals("新顾客")).map(m -> m.get("customer_code")).filter(Objects::nonNull).collect(Collectors.toSet()).size();
            oldCustomQty = maps.stream().filter(m -> m.get("newOrOld") != null && m.get("newOrOld").equals("老顾客")).map(m -> m.get("customer_code")).filter(Objects::nonNull).collect(Collectors.toSet()).size();
        } else {
            Response<RegionDetailVO> regionDetailVOResponse = regionRemoteService.selectById(Long.valueOf(query.getRegionCode()));
            if (regionDetailVOResponse.isSuccess() && regionDetailVOResponse.getData() != null) {
                vo.setRegionName(regionDetailVOResponse.getData().getName());
            }
            // 具体省份
            List<Map> provinceMaps = maps.stream().filter(e -> e.get("provinceCode").equals(Long.valueOf(query.getRegionCode()))).collect(Collectors.toList());
            maps = provinceMaps;
            newCustomQty = provinceMaps.stream().filter(m -> m.get("newOrOld") != null && m.get("newOrOld").equals("新顾客")).map(m -> m.get("customer_code")).filter(Objects::nonNull).collect(Collectors.toSet()).size();
            oldCustomQty = provinceMaps.stream().filter(m -> m.get("newOrOld") != null && m.get("newOrOld").equals("老顾客")).map(m -> m.get("customer_code")).filter(Objects::nonNull).collect(Collectors.toSet()).size();
            if (totalNum != 0) {
                vo.setSalesVolume(BigDecimal.valueOf(100 * (newCustomQty + oldCustomQty) / totalNum).setScale(2, RoundingMode.HALF_UP));
            } else {
                vo.setSalesVolume(BigDecimal.ZERO);
            }
        }*/
        vo.setRegionName("全国").setSalesVolume(BigDecimal.valueOf(100));
        newCustomQty = maps.stream().filter(m -> m.get("newOrOld") != null && m.get("newOrOld").equals("新顾客")).map(m -> m.get("customer_code")).filter(Objects::nonNull).collect(Collectors.toSet()).size();
        oldCustomQty = maps.stream().filter(m -> m.get("newOrOld") != null && m.get("newOrOld").equals("老顾客")).map(m -> m.get("customer_code")).filter(Objects::nonNull).collect(Collectors.toSet()).size();
        //总客数
        totalCustomQty = newCustomQty + oldCustomQty;
        List<Object> data = new ArrayList<>();
        List<Object> data2 = new ArrayList<>();
        // 饼图
        data.add("新客占比");
        if (totalCustomQty != 0) {
            data.add(BigDecimal.valueOf(100 * newCustomQty / totalCustomQty).setScale(2, RoundingMode.HALF_UP));
        } else {
            data.add(BigDecimal.ZERO);
        }
        datas.add(data);
        data2.add("老客占比");
        if (totalCustomQty != 0) {
            data2.add(BigDecimal.valueOf(100 * oldCustomQty / totalCustomQty).setScale(2, RoundingMode.HALF_UP));
        } else {
            data2.add(BigDecimal.ZERO);
        }
        datas.add(data2);

        // 城市顾客占比排名前五
        List<Map<String, Object>> provinceMaps = maps.stream()
                .collect(Collectors.groupingBy(
                        map -> map.get("provinceCode"),
                        Collectors.groupingBy(
                                map -> map.get("newOrOld"),
                                Collectors.mapping(
                                        map -> (String) map.get("customer_code"),
                                        Collectors.collectingAndThen(
                                                Collectors.toSet(),
                                                set -> (long) set.size()
                                        )
                                )
                        )
                ))
                .entrySet().stream()
                .map(entry -> {
                    Map<String, Object> result = new HashMap<>();
                    result.put("provinceCode", entry.getKey());
                    result.put("newOrOldCount", entry.getValue());
                    return result;
                })
                .sorted(Comparator.comparing(
                        (Map<String, Object> map) -> {
                            Map<Object, Long> newOrOldCount = (Map<Object, Long>) map.get("newOrOldCount");
                            return newOrOldCount.values().stream().mapToLong(Long::longValue).sum();
                        },
                        Comparator.reverseOrder()
                ))
                .collect(Collectors.toList());


        // 将分组后的结果转换为列表并按 numberOfTransactions 字段倒序排序
//        sortedList.removeIf(e -> e.getKey().equals("-99") || e.getKey().isEmpty());
        // 获取所有省份
        RegionListQry regionListQry = new RegionListQry();
        regionListQry.setType(2);
        Response<List<RegionVO>> regionResponse = regionRemoteService.selectList(regionListQry);
        if (!regionResponse.isSuccess() || regionResponse.getData().isEmpty()) {
            return chart;
        }
        List<RegionVO> regionList = regionResponse.getData();
        // 截取前五个条目
        List<Map<String, Object>> topFiveMaps = provinceMaps.stream()
                .limit(5)
                .collect(Collectors.toList());
        for (Map<String, Object> map : topFiveMaps) {
            RegionalCityVO regionalCity = new RegionalCityVO();
            Map<String, Long> newOrOldCount = (Map<String, Long>) map.get("newOrOldCount");
            // 计算 sum，确保新旧计数的总和
            double totalQty = newOrOldCount.values().stream()
                    .mapToDouble(Long::doubleValue)
                    .sum();
            // 整体顾客占比
            if (totalNum != 0) {
                regionalCity.setTotalCustomProportion(BigDecimal.valueOf(100 * totalQty / totalNum).setScale(2, RoundingMode.HALF_UP));
            } else {
                continue;
            }
            if (totalQty != 0) {
                double tran = 0;
                if (newOrOldCount.get("新顾客") != null) {
                    tran = newOrOldCount.get("新顾客");
                    regionalCity.setNewCustomProportion(BigDecimal.valueOf(100 * tran / totalQty).setScale(2, RoundingMode.HALF_UP));
                }else{
                    regionalCity.setNewCustomProportion(BigDecimal.ZERO);
                }
                if (newOrOldCount.get("老顾客") != null) {
                    tran = newOrOldCount.get("老顾客");
                    regionalCity.setOldCustomProportion(BigDecimal.valueOf(100 * tran / totalQty).setScale(2, RoundingMode.HALF_UP));
                }else{
                    regionalCity.setOldCustomProportion(BigDecimal.ZERO);
                }
            } else {
                continue;
            }
            // 省份名称
            regionList.stream().filter(regionVO -> regionVO.getId().equals(map.get("provinceCode"))).findFirst().ifPresent(e -> regionalCity.setCityName(e.getName()));
            // 省份编码
            regionalCity.setCityCode(map.get("provinceCode").toString());
            cityTableData.add(regionalCity);
        }

        chart.setItems(items).setDatas(datas);
        vo.setCityTableData(cityTableData);
        chart.setExtend(vo);
        return chart;
    }
    public List<Map> getToBCustomerList(List<BizCustomerVO> bizCustomers,List<String> bizCodes, Long orgId) {
        // 查全部
        String year = String.valueOf(LocalDate.now().getYear());
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM ads_base_biz_customer WHERE  org_id = ")
                .append(orgId)
                .append(" and year = ")
                .append(year);
        if (ObjectUtils.isNotEmpty(bizCodes)){
            sql.append(" and customer_code in (");
            for (String bizCode : bizCodes) {
                sql.append("\"")
                        .append(bizCode)
                        .append("\"")
                        .append(",");
            }
            sql.deleteCharAt(sql.length() - 1);
            sql.append(")");
        }
//        sql.append("group by customer_id ORDER BY number_of_dealing");
        List<Map> maps = jdbcService.selectList(sql.toString(), new Object[]{});

        // 给map赋值省份id、省份名称、客户类型和合作类型、新老客
        if (ObjectUtils.isNotEmpty(bizCustomers)) {
            for (Map map : maps) {
                bizCustomers.stream().filter(e -> e.getCode().equals(map.get("customer_code"))).findFirst().ifPresent(biz -> {
                    map.put("provinceCode", biz.getProvinceId());
                    map.put("provinceName", biz.getProvinceName());
                    map.put("type", biz.getType());
                    map.put("newOrOld", biz.getRemark());
//                map.put("coopType", biz.getCoopType());
                });
            }
        }
        maps = maps.stream().filter(e -> ObjectUtils.isNotEmpty(e.get("provinceCode"))).collect(Collectors.toList());
        return maps;
    }

    public void markNewOrOldCustomer(List<BizCustomerVO> bizCustomers) {
        LocalDateTime now = LocalDateTime.now();

        for (BizCustomerVO customer : bizCustomers) {
            LocalDateTime firstOrderTime = customer.getFirstOrderTime();
            if (firstOrderTime != null) {
                long daysBetween = ChronoUnit.DAYS.between(firstOrderTime, now);
                if (daysBetween <= 365) {
                    customer.setRemark("新顾客");
                } else {
                    customer.setRemark("老顾客");
                }
            } /*else {
                // 处理 firstOrderTime 为 null 的情况
                customer.setRemark("未知");
            }*/
        }
    }
    @Override
    public String getReportName() {
        return "ToB区域顾客占比";
    }
}
